import pandas as pd 

from sklearn.preprocessing import MinMaxScaler 

import pymysql 

import pymysql.cursors 

class MysqlUtils(object): 

def __init__(self, *args): 

self.conn = pymysql.connect( 

host='127.0.0.1', 

user='root', 

password='root', 

db='scenic', 

port=3306, 

charset='utf8' 

) 

def is_holiday(self, date): 

"""是否节假日判断 

""" 

def get_scenic_data(self): 

"""获取数据 

""" 

cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) 

sql = """ 

SELECT DATE(g.create_time) as date, HOUR(g.create_time) as count FROM order_user_gate_rel g w 

""" 

cursor.execute(sql) 

ret = cursor.fetchall() 

df = pd.DataFrame(ret) 

#print(df.head) 

#格式转换 

date_range = pd.date_range(start='2024-07-01', end='2025-03-02', freq='D') 

hours = range(6, 24) 

full_index =pd.MultiIndex.from_product([date_range, hours], names=['date', 'hour']) 

#print(full_index) 

df_full = df.set_index(['date', 'hour']).reindex(full_index, fill_value=0).reset_index() 

# 按天组织数据，每行包含18小时的检票次数 

df_pivot = df_full.pivot(index='date', columns='hour', values='count') 

# print(df_pivot) 

df_pivot['dow'] = df_pivot.index.dayofweek 

df_pivot['month'] = df_pivot.index.month 

#print(df_pivot) 

df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday) 

# 对星期几 

df_pivot = pd.get_dummies(df_pivot, columns=['doe', 'month'], dtype=int) 

#归一化 

hours_columns = list(range(6, 24)) 

df_hours = df_pivot[hours_columns].copy() 

feature_colunms = [col for col in df_pivot.columns if col not in hours_columns] 

df_feature = df_pivot[feature_colunms].copy() 

scaler = MinMaxScaler() 

scaled_hours = scaler.fit_transform(df_hours) 

#将 

df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index) 

#合并 

df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1) 

print(df_pivot_clean) 

df_pivot_clean.to_csv('NN/scenic_data.csv', index=False) 

if __name__ == '__main__': 

mu = MysqlUtils() 

mu.get_scenic_data()